HW#3 --- last modified March 02 2019 21:20:26..

Solution set.

Due date: Oct 24

Files to be submitted:
  Problems.pdf
  Dungeon.sql
  Dungeon.mdb

Purpose: To get practice writing queries in the relational algebra, tuple, and domain calculus. To practice going from an ER model to actual tables in Oracle and Access.

Specification:

To begin write each of the express each of the following queries in (a) the relational algebra, (b) the tuple relational calculus, and (c) in the domain relational calculus. The underlying database schema is that of the COMPANY described in the book. Submit your answer in Problems.pdf.

1. Retrieve all EMPLOYEEs who earn as much as some EMPLOYEE in the Research DEPARTMENT.

2. For each BDATE value of some EMPLOYEE count the number of EMPLOYEEs with that BDATE. (For this one, you don't have to write calculus expressions)

3. Retrieve all the DEPENDANTs of EMPLOYEEs who supervise at least two other EMPLOYEEs.

4. Retrieve all the EMPLOYEEs who work on all of the PROJECTs located in the Engineering DEPARTMENT.

5. Retrieve the first name and last names of EMPLOYEEs who either have the highest or the lowest salaries in their DEPARTMENTs. (Do not use aggregation).

Next imagine you are on the road to becoming a total geek. You have been placed in charge of coming up with a database for a Dungeons and Dragons conference (Google if never heard of). Besides needing to know the usual name and address, etc of PARTICIPANTs, you need to model that PARTICIPANT may play in several GAMEs, and for each GAME they may play different CHARACTERs. Some PARTICIPANTs might also be DUNGEON MASTERS for some games. There might even be an OVERALL_STANDINGS and some PARTICPANTs might receive different kinds of AWARDS. In addition to PARTICIPANTs, there might be various VENDORs, selling D&D MERCHANDISE. VENDORs might rent BOOTHs and sponsor certain SPECIAL GAMES. Model this situation with an ER or EER diagram which you also should put in Problems.pdf. Then using the ER to Relational mapping algorithm convert this diagram to a relational database schema. Be sure to incorporate all applicable constraints. Put this schema as well in Problems.pdf. Next create a script Dungeon.sql which creates all of the tables of your schema in SQL so that it could be run in your Oracle account. Also, include in your script commands to insert at least 5 rows into every table you have. Finally, using Microsoft Access (available in the labs) recreate your database in a file Dungeon.mdb. Again, be sure to incorporate all constraints (in particular, foreign key constraints).

Point Breakdown